package demo2;

import demo1.JDBCUtil;
import demo1.User;
import org.junit.Test;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

/**
 * @description: 封装了数据表的操作
 * @author: apple
 * @date:  2022/1/28 7:50 下午
 * @version 1.0
 **/
public abstract class BaseDAO {

    @Test
    public void test() throws SQLException, IOException, NoSuchFieldException, IllegalAccessException, InstantiationException {

        final Connection connection = JDBCUtil.getConnection();
        String sql = "select * from user_table where name = ?";
        final User ccc = getInstance(connection, User.class, sql, "ccc");
        System.out.println(ccc);

        JDBCUtil.closeConnection(connection,null);
    }

    @Test
    public void test2() throws SQLException, IOException, NoSuchFieldException, IllegalAccessException, InstantiationException {

        final Connection connection = JDBCUtil.getConnection();
        String sql = "select count(*) from user_table";
        final Object value = getValue(connection, sql);
        System.out.println(value);

        JDBCUtil.closeConnection(connection,null);
    }

    // 考虑事务的版本：2.0
    private int update(Connection connection, String sql , Object...params) throws SQLException, ParseException, IOException {

        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1, params[i]);
            }
            return preparedStatement.executeUpdate();
        }catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(null,preparedStatement);
        }
        return 0;
    }

    // 考虑事务的版本：2.0
    public <T> T getInstance(final Connection connection, Class<T> clazz, String sql , Object...params) throws SQLException, IOException, NoSuchFieldException, IllegalAccessException, InstantiationException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                if (params[i] == null) {
                    continue;
                }
                preparedStatement.setObject(i+1,params[i]);
            }
            resultSet = preparedStatement.executeQuery();
            // 拿到结果集的元数据，用于获取表的列数等信息
            final ResultSetMetaData metaData = resultSet.getMetaData();
            final int columnCount = metaData.getColumnCount();
            if (resultSet.next()) {
                // 泛型的实例只能通过运行时类来创建,创建实例，类型为T类型（泛型）
                final T t = clazz.newInstance();
                System.out.println(t.getClass());
                for (int i = 0; i < columnCount; i++) {
                    final Object columnValue = resultSet.getObject(i + 1);
                    // 获取列的列名，但获取不了列的别名
                    final String columnName = metaData.getColumnName(i + 1);
                    // 调用student对象的columnName列，设置为columnValue
                    final Field f1 = clazz.getDeclaredField(columnName);
                    f1.setAccessible(true);
                    f1.set(t,columnValue);
                }
                return t;
            }

        } finally {
            JDBCUtil.closeConnection(null,preparedStatement,resultSet);
        }
        return null;
    }

    public <T> List<T> getForList(final Connection connection,Class<T> clazz, String sql , Object...params) throws SQLException, IOException, NoSuchFieldException, IllegalAccessException, InstantiationException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                if (params[i] == null) {
                    continue;
                }
                preparedStatement.setObject(i+1,params[i]);
            }
            resultSet = preparedStatement.executeQuery();
            // 拿到结果集的元数据，用于获取表的列数等信息
            final ResultSetMetaData metaData = resultSet.getMetaData();
            final int columnCount = metaData.getColumnCount();
            List<T> list = new ArrayList<>();
            while (resultSet.next()) {
                // 泛型的实例只能通过运行时类来创建,创建实例，类型为T类型（泛型）
                final T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    final Object columnValue = resultSet.getObject(i + 1);
                    // 获取列的列名，但获取不了列的别名
                    final String columnName = metaData.getColumnName(i + 1);
                    // 调用student对象的columnName列，设置为columnValue
                    final Field f1 = clazz.getDeclaredField(columnName);
                    f1.setAccessible(true);
                    f1.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } finally {
            JDBCUtil.closeConnection(null,preparedStatement,resultSet);
        }
    }

    public <T> T getValue(Connection connection , String sql , Object...params) throws SQLException, IOException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T value = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                if (params[i] == null) {
                    continue;
                }
                preparedStatement.setObject(i+1,params[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                value = (T)resultSet.getObject(1);
            }
            return  value;
        } finally {
            JDBCUtil.closeConnection(null,preparedStatement,resultSet);
        }
    }
}
